package com.jzwl.system.admin.sysuser.dao;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.jzwl.system.admin.user.pojo.BusyShopuser;
import com.jzwl.system.base.dao.BaseDAO;
import com.jzwl.common.id.IdFactory;
import com.jzwl.common.page.PageObject;

@Repository("v2SysuserDao")
public class SysuserDao {

	@Autowired
	private BaseDAO baseDAO;// dao基类，操作数据库

	@Autowired
	private IdFactory idFactory;

	public boolean addSysuser(Map<String, Object> map) {
		// 自动注入时间戳为ID 酌情修改数据库类型为bigint int会越界
		map.put("id", idFactory.nextId());

		String sql = "insert into v2_busy_shopuser "
				+ " (id,username,password,sex,mobile,wxId,openId,address,citycode,status,isDelete,createdTime,departmentId) "
				+ " values "
				+ " (:id,:username,:password,:sex,:mobile,:wxId,:openId,:address,:citycode,:status,:isDelete,:createdTime,:departmentId)";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public String getColumns() {
		return "" + " t.id as id," + " t.username as username,"
				+ " t.password as password," + " t.sex as sex,"
				+ " t.mobile as mobile," + " t.wxId as wxId,"
				+ " t.openId as openId," + " t.address as address,"
				+ " t.citycode as citycode," + " t.status as status,"
				+ " t.isDelete as isDelete," + " t.createdTime as createdTime,"
				+ " pc.dic_name as cityname,"
				+ " d.departmentname as departmentname";
	}

	public PageObject querySysuserList(Map<String, Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		String sql = "select "
				+ getColumns()
				+ " from v2_busy_shopuser t "
				+ "left join v2_dic_data pc on t.citycode = pc.dic_value "
				// + " LEFT JOIN v2_plat_userrole pu ON pu.userId = t.id "
				// + " LEFT JOIN v2_plat_role pr ON pr.roleId = pu.roleId "
				+ " LEFT JOIN v2_department d ON d.departmentId = t.departmentId "
				+ "where 1=1 and t.username <> 'superadmin' ";

		if (null != map.get("username")
				&& StringUtils.isNotEmpty(map.get("username").toString())) {
			sql = sql + " and t.username  like '%" + map.get("username")
					+ "%' ";
		}
		if (null != map.get("password")
				&& StringUtils.isNotEmpty(map.get("password").toString())) {
			sql = sql + " and t.password  = " + map.get("password") + "";
		}
		if (null != map.get("sex")
				&& StringUtils.isNotEmpty(map.get("sex").toString())) {
			sql = sql + " and t.sex  = " + map.get("sex") + "";
		}
		if (null != map.get("mobile")
				&& StringUtils.isNotEmpty(map.get("mobile").toString())) {
			sql = sql + " and t.mobile  = " + map.get("mobile") + "";
		}
		if (null != map.get("wxId")
				&& StringUtils.isNotEmpty(map.get("wxId").toString())) {
			sql = sql + " and t.wxId  = " + map.get("wxId") + "";
		}
		if (null != map.get("openId")
				&& StringUtils.isNotEmpty(map.get("openId").toString())) {
			sql = sql + " and t.openId  = " + map.get("openId") + "";
		}
		if (null != map.get("address")
				&& StringUtils.isNotEmpty(map.get("address").toString())) {
			sql = sql + " and t.address  = " + map.get("address") + "";
		}
		if (null != map.get("citycode")
				&& StringUtils.isNotEmpty(map.get("citycode").toString())) {
			sql = sql + " and t.citycode  = " + map.get("citycode") + "";
		}
		if (null != map.get("status")
				&& StringUtils.isNotEmpty(map.get("status").toString())) {
			sql = sql + " and t.status  = " + map.get("status") + "";
		}
		if (null != map.get("isDelete")
				&& StringUtils.isNotEmpty(map.get("isDelete").toString())) {
			sql = sql + " and t.isDelete  = " + map.get("isDelete") + "";
		}
		if (null != map.get("createdTime")
				&& StringUtils.isNotEmpty(map.get("createdTime").toString())) {
			sql = sql + " and t.createdTime >= " + map.get("createdTimeBegin")
					+ "";
			sql = sql + " and t.createdTime <= " + map.get("createdTimeEnd")
					+ "";
		}

		sql = sql + " order by t.createdTime desc";

		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return po;
	}

	public boolean updateSysuser(Map<String, Object> map) {

		String sql = "update v2_busy_shopuser set "
				+ " username=:username,sex=:sex,mobile=:mobile,wxId=:wxId,openId=:openId,address=:address,citycode=:citycode,status=:status,departmentId=:departmentId "
				+ " where id=:id";

		return baseDAO.executeNamedCommand(sql, map);
	}

	//根据id获取单个用户的信息
	@SuppressWarnings("unchecked")
	public Map<String, Object> getSysuserById(Map<String, Object> map) {

		String sql = "select t.id,t.username,t.password,t.sex,t.mobile,t.wxId,t.openId,t.address,t.citycode,t.status,d.departmentname,d.departmentId "
				+ " from v2_busy_shopuser t LEFT JOIN v2_department d ON d.departmentId = t.departmentId "
				+ "where t.id = " + map.get("id") + "";

		Map m = baseDAO.queryForMap(sql);

		return m;

	}

	public boolean updatePasswordSysuser(Map<String, Object> map) {

		String sql = "update v2_busy_shopuser set " + " password=:password "
				+ " where id=:id";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public boolean deleteSysuser(Map<String, Object> map) {

		String sql = "update v2_busy_shopuser set isDelete=1 where id=:id";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public Map<String, Object> queryUser(Map<String, Object> map) {
		String sql = "select u.*,ur.roleId from v2_plat_user u left join v2_plat_userrole ur on u.userId=ur.userId where u.username=:username and u.password=:password";
		return baseDAO.queryForMap(sql, map);
	}

	public List<Map<String, Object>> queryRoleResourceList(String roleId) {
		String sql = "select re.* from v2_plat_roleresource rr left join v2_plat_resource re on rr.resourceId=re.resourceId where rr.roleId="
				+ roleId + " and re.status=1";
		return baseDAO.queryForList(sql);
	}

	public List<Map<String, Object>> queryUserByName(String username) {
		String sql = "select u.*,ur.roleId from v2_plat_user u left join v2_plat_userrole ur on u.userId=ur.userId where u.username='"
				+ username + "'";
		return baseDAO.queryForList(sql);
	}

	public List<Map<String, Object>> findAuthorityByUserId(String userId) {
		String sql = "SELECT a.* FROM v2_plat_roleauthority ra left join v2_plat_authority a on ra.authorityId = a.authorityId left join plat_userrole ru on ra.roleId = ru.roleId WHERE ru.userId = "
				+ userId;
		return baseDAO.queryForList(sql);
	}

	public List<Map<String, Object>> queryUserResourceList(String userId) {
		String sql = "select re.* from v2_plat_authresource ar "
				+ "left join v2_plat_resource re on ar.resourceId=re.resourceId "
				+ "left join v2_plat_roleauthority ra on ra.authorityId = ar.authorityId "
				+ "left join v2_plat_userrole ur on ur.roleId = ra.roleId "
				+ "left join v2_busy_shopuser u on u.id = ur.userId "
				+ "where ur.userId=" + userId
				+ " and re.status=1 and re.type!=1";
		return baseDAO.queryForList(sql);
	}

	public List<Map<String, Object>> queryConsultList(
			Map<String, Object> paramsMap) {
		String sql = "SELECT u.userId value,u.realname name FROM v2_plat_userrole s "
				+ " INNER JOIN plat_user u ON s.userId = u.userId "
				+ " WHERE s.roleId = 61";
		return baseDAO.queryForList(sql);
	}

	public String getShopColumns() {
		return "" + " t.shopId as shopId," + " t.shopname as shopname,"
				+ " t.shopclassId as shopclassId,"
				+ " t.industrytype as industrytype," + " t.phone as phone,"
				+ " t.address as address," + " t.longitude as longitude,"
				+ " t.latitude as latitude," + " t.ownerId as ownerId,"
				+ " t.sortId as sortId," + " t.isDelete as isDelete,"
				+ " t.description as description," + " t.citycode as citycode,"
				+ " c.name as cityname";
	}

	public PageObject queryCityInShopList(Map<String, Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		String sql = "select "
				+ getShopColumns()
				+ " from v2_busy_shop_basicinfo t "
				+ " left join v2_plat_city c on t.citycode = c.code "
				+ " left join v2_busy_shop_user_basicinfo ub on t.shopId = ub.shopId where 1=1 "
				+ " and t.isDelete = 0 ";

		if (null != map.get("shopname")
				&& StringUtils.isNotEmpty(map.get("shopname").toString())) {
			sql = sql + " and t.shopname  like '%" + map.get("shopname")
					+ "%' ";
		}
		if (null != map.get("shopclassId")
				&& StringUtils.isNotEmpty(map.get("shopclassId").toString())) {
			sql = sql + " and t.shopclassId  = " + map.get("shopclassId") + "";
		}
		if (null != map.get("industrytype")
				&& StringUtils.isNotEmpty(map.get("industrytype").toString())) {
			sql = sql + " and t.industrytype  = " + map.get("industrytype")
					+ "";
		}
		if (null != map.get("phone")
				&& StringUtils.isNotEmpty(map.get("phone").toString())) {
			sql = sql + " and t.phone  like '%" + map.get("phone") + "%' ";
		}
		if (null != map.get("address")
				&& StringUtils.isNotEmpty(map.get("address").toString())) {
			sql = sql + " and t.address  like '%" + map.get("address") + "%' ";
		}
		if (null != map.get("longitude")
				&& StringUtils.isNotEmpty(map.get("longitude").toString())) {
			sql = sql + " and t.longitude  = " + map.get("longitude") + "";
		}
		if (null != map.get("latitude")
				&& StringUtils.isNotEmpty(map.get("latitude").toString())) {
			sql = sql + " and t.latitude  = " + map.get("latitude") + "";
		}
		if (null != map.get("ownerId")
				&& StringUtils.isNotEmpty(map.get("ownerId").toString())) {
			sql = sql + " and t.ownerId  = " + map.get("ownerId") + "";
		}
		if (null != map.get("sortId")
				&& StringUtils.isNotEmpty(map.get("sortId").toString())) {
			sql = sql + " and t.sortId  = " + map.get("sortId") + "";
		}
		if (null != map.get("isDelete")
				&& StringUtils.isNotEmpty(map.get("isDelete").toString())) {
			sql = sql + " and t.isDelete  = " + map.get("isDelete") + "";
		}
		if (null != map.get("description")
				&& StringUtils.isNotEmpty(map.get("description").toString())) {
			sql = sql + " and t.description  = " + map.get("description") + "";
		}
		if (null != map.get("citycode")
				&& StringUtils.isNotEmpty(map.get("citycode").toString())) {
			sql = sql + " and t.citycode  = " + map.get("citycode") + "";
		}

		sql = sql + " and ub.shopuserId=" + map.get("id") + "";
		sql = sql + " order by t.shopclassId,t.sortId ";

		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return po;
	}

	public boolean updateDepartmentname(Map<String, Object> map) {

		String sql = "update v2_department set "
				+ " departmentname=:departmentname "
				+ " where departmentId=:departmentId";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public PageObject queryNoCityShopList(Map<String, Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		String sql = "select b.*,c.name as cityname from v2_busy_shop_basicinfo b "
				+ " left join v2_plat_city c on b.citycode = c.code "
				+ "where b.shopId not in";
		sql = sql + "(select t.shopId as shopId from v2_busy_shop_basicinfo t ";
		// 关联权限资源表查询
		sql = sql
				+ " left join v2_busy_shop_user_basicinfo ub on t.shopId = ub.shopId where 1=1 ";

		sql = sql + " and ub.shopuserId=" + map.get("id") + ") ";
		sql = sql + " and b.isDelete=0 and b.citycode=" + map.get("citycode")
				+ "" + " order by b.shopclassId,b.sortId ";

		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);

		return po;
	}

	public boolean addShopToUser(Map<String, Object> map) {

		// 自动注入时间戳为ID 酌情修改数据库类型为bigint int会越界
		// map.put("resourceId", Sequence.nextId());

		String sql = "insert into v2_busy_shop_user_basicinfo "
				+ " (shopId,shopuserId,isMaster) " + " values "
				+ " (:shopId,:id,0)";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public boolean deleteShopToUser(Map<String, Object> map) {

		String sql = "delete from v2_busy_shop_user_basicinfo where shopId=:shopId and shopuserId=:id ";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public List<Map<String, Object>> queryUserShop(Map<String, Object> paramsMap) {
		String sql = "SELECT * FROM v2_busy_shop_user_basicinfo sub WHERE sub.shopuserId = "
				+ paramsMap.get("id");
		return baseDAO.queryForList(sql);
	}

	public Map<String, Object> queryUserRole(Map<String, Object> map) {
		String sql = "select u.*,ur.roleId from v2_busy_shopuser u left join plat_userrole ur on u.id=ur.userId where u.id=:id";
		return baseDAO.queryForMap(sql, map);
	}

	public boolean addShopToUserDZ(Map<String, Object> map) {

		// 自动注入时间戳为ID 酌情修改数据库类型为bigint int会越界
		// map.put("resourceId", Sequence.nextId());
		// 1.修改店铺基本表busy_shop_basicinfo 字段ownerId
		String sql1 = "update v2_busy_shop_basicinfo set ownerId = :id where shopId =:shopId";
		boolean flag1 = baseDAO.executeNamedCommand(sql1, map);

		// 2.查询busy_shop_user_basicinfo中是否存在当前店铺与店长角色用户的关系
		String sql2 = "select * from v2_busy_shop_user_basicinfo where shopId=:shopId and isMaster=1";
		Map<String, Object> resultMap = baseDAO.queryForMap(sql2, map);
		if (null != resultMap && !resultMap.isEmpty()) {
			// 存在修改已存在的记录
			String sql3 = "update v2_busy_shop_user_basicinfo set shopuserId=:id where shopId=:shopId and isMaster=1";
			boolean flag3 = baseDAO.executeNamedCommand(sql3, map);
			return flag3;
		} else {
			// 不存在插入一条记录
			String sql4 = "insert into v2_busy_shop_user_basicinfo(shopId,shopuserId,isMaster) values(:shopId,:id,1)";
			boolean flag4 = baseDAO.executeNamedCommand(sql4, map);
			return flag4;
		}

	}

	/**
	 * 查询用户已拥有角色
	 * 
	 * @param paramsMap
	 * @return
	 */
	public PageObject querySysroleUserList(Map<String, Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		String sql = "select t.* from v2_plat_role t "
				+ "left join v2_plat_userrole ur on t.roleId = ur.roleId "
				+ "where 1=1 ";
		if (null != map.get("status")
				&& StringUtils.isNotEmpty(map.get("status").toString())) {
			sql = sql + " and t.status  = " + map.get("status") + "";
		}
		sql = sql + " and ur.userId = " + map.get("userId");
		sql = sql + " order by t.roleId";
		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);
		return po;
	}

	/**
	 * 查询用户未拥有角色
	 * 
	 * @param paramsMap
	 * @return
	 */
	public PageObject querySysroleNoUserList(Map<String, Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性
		String sql = "select * from v2_plat_role where roleId not in "
				+ "(select t.roleId from v2_plat_role t "
				+ " left join v2_plat_userrole ur on t.roleId = ur.roleId where 1=1 ";
		sql = sql + " and t.status = 1 and ur.userId = " + map.get("userId");
		sql = sql + ")" + " order by roleId";
		PageObject po = baseDAO.queryForMPageList(sql, new Object[] {}, map);
		return po;
	}

	/**
	 * 删除用户角色关系
	 * 
	 * @param paramsMap
	 * @return
	 */
	public boolean deleteSysroleUser(Map<String, Object> map) {
		String sql = "delete from v2_plat_userrole where roleId = :roleId and userId = :userId ";
		return baseDAO.executeNamedCommand(sql, map);
	}

	/**
	 * 添加用户角色关系
	 * 
	 * @param paramsMap
	 * @return
	 */
	public boolean addSysroleUser(Map<String, Object> map) {
		// 自动注入时间戳为ID 酌情修改数据库类型为bigint int会越界
		// map.put("roleId", Sequence.nextId());
		String sql = "insert into v2_plat_userrole "
				+ " (userId,roleId,createdTime) " + " values "
				+ " (:userId,:roleId,:createdTime)";
		return baseDAO.executeNamedCommand(sql, map);
	}

	/**
	 * 根据用户名查询用户
	 * 
	 * @param name
	 * @return
	 */
	public Map<String, Object> findUserByName(String name) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT(u.id) as id ,u.username, u.`password`,u.`sex`,u.`mobile`,u.`wxId`,u.`citycode`,case d.`cityName` when '市辖区' then d.provinceName else d.`cityName` end as cityName,u.`departmentId`, u.`status`");
		sql.append(" FROM v2_busy_shopuser u");
		sql.append(" LEFT JOIN  v2_district_info d ON u.citycode = d.cityCode ");
		sql.append(" WHERE u.`status` = 1 AND u.isDelete = 0 ");
		sql.append(" AND u.username = '" + name + "'");

		return baseDAO.queryForMap(sql.toString());
	}

	/**
	 * 根据用户ID重新用户的权限
	 * 
	 * @param userId
	 * @return
	 */
	public List<Map<String, Object>> findPrivilegesByUserId(String userId)
			throws Exception {
		StringBuffer sql = new StringBuffer(
				"SELECT a.authorityId, a.authorityCode FROM v2_busy_shopuser u ");// ,
																					// res.resourceUrl
		sql.append(" LEFT JOIN v2_plat_userrole ur ON ur.userId = u.id");
		sql.append(" LEFT JOIN v2_plat_role r ON r.roleId = ur.roleId");
		sql.append(" LEFT JOIN v2_plat_roleauthority ra ON ra.roleId = r.roleId");
		sql.append(" LEFT JOIN v2_plat_authority a ON ra.authorityId = a.authorityId");
		sql.append(" LEFT JOIN v2_plat_authresource ar ON ar.authorityId = ra.authorityId");
		// sql.append(" LEFT JOIN v2_plat_resource res ON res.resourceId = ar.resourceId");
		sql.append(" WHERE u.id = ?");
		sql.append(" AND r.`status` = 1");
		// sql.append(" AND res.`status` = 1");
		return baseDAO.getJdbcTemplate().queryForList(sql.toString(),
				new Object[] { userId });
	}

	/**
	 * 根据用户名判断用户是否已存在
	 * 
	 * @param username
	 * @return
	 */
	public boolean isUserLive(String username) {
		String sql = "select * from v2_busy_shopuser where username = '"
				+ username + "' and isDelete = '0'";
		Map<String, Object> userMap = baseDAO.queryForMap(sql);
		boolean flag = userMap.isEmpty();
		return flag;
	}

	/**
	 * 根据userId获取用户角色信息
	 * 
	 * @param userId
	 * @return
	 */
	public Map findRolerByUserId(String userId) {
		String sql = " select pr.name from v2_busy_shopuser bs "
				+ "    left join v2_plat_userrole pu on bs.id=pu.userId "
				+ "    left join v2_plat_role pr on pu.roleId=pr.roleId  "
				+ " where bs.id=" + userId + "";
		return baseDAO.queryForMap(sql);
	}
//getuser
	
	

}
